<!DOCTYPE html>
<html xmlns:th="http://www.thymeleaf.org"
>
<head th:include="includes/head :: htmlhead(~{::title}, ~{})">
    <meta charset="utf-8">
    <title>表数据&维护</title>
</head>
<body>
<div id="mainLayout" class="easyui-layout" data-options="fit: true">
    <input id="filterCondition" name="filterCondition" class="easyui-textbox" type="text" style="width:100%"
           data-options="prompt:'输入查询条件查询数据(不必带where)',
                    iconAlign:'left',
                    icons:[{
                        iconCls:'icon-search',
                        handler: function(e){
                            let value = $(e.data.target).textbox('getValue');
                            queryByCondition(value);
                        }
                    }]"/>
    <div style="padding:0px;height:calc(100% - 60px)">
        <table id='dg' style="width:100%;height:100%"></table>
    </div>
    <div id="pg" style="height:30px" class="easyui-pagination">
    </div>
</div>

<!-- ddl模块 -->
<div id="ddlDlg" class="easyui-dialog" maximized="true"
     closed="true" closable="true" modal="true">
    <input id="ddlTb" type="text" style="width:100%;height:100%" readonly>
</div>

<!-- 列模式模块 -->
<div id="columnModeDlg" class="easyui-dialog" maximized="true"
     closed="true" closable="true" modal="true">
    <div id="columnModeTable">
    </div>
</div>

<!-- sql模块 -->
<div id="sqlDlg" class="easyui-dialog" maximized="false"
     closed="true" closable="true" modal="true">
    <input id="sqlTb" type="text" style="width:500px;height:300px" readonly>
</div>


<script type="text/javascript">
    let ctxPath = '[[${#servletContext.contextPath}]]';
    let table = '[[${table}]]';
    let databaseRandomStr = '[[${databaseRandomStr}]]';
    let primaryKey;
    let columnType;
    $(function () {
        let toolbar = [{
            text: 'DDL',
            iconCls: 'icon-diy-table2',
            handler: function () {
                ddl();
            }
        }, {
            text: '列模式',
            iconCls: 'icon-diy-column1',
            handler: function () {
                columnMode();
            }
        }, {
            text: 'INSERT语句',
            iconCls: 'icon-diy-insert1',
            handler: function () {
                genralInsertSql();
            }
        }, {
            text: 'UPDTAE语句',
            iconCls: 'icon-diy-update1',
            handler: function () {
                genralUpdateSql();
            }
        }, {
            text: 'DELETE语句',
            iconCls: 'icon-diy-delete2',
            handler: function () {
                genralDeleteSql();
            }
        }, {
            text: '下载(最多1000条)',
            iconCls: 'icon-diy-export1',
            handler: function () {
                exportData();
            }
        }];

        $('#dg').datagrid({
            data: []
            , columns: []
            , autoRowHeight: true
            , rownumbers: true
            , singleSelect: true
            , toolbar: toolbar
            , onSortColumn: function (sort, order) {
                //alert(sort + '    ' + order);
                let condition = $("#filterCondition").textbox('getValue');
                pagingList(1, COMMONPARAM_DEFAULTPAGESIZE, condition, sort + ' ' + order);
            }
        });

        $('#pg').pagination({
            pageList: COMMONPARAM_PAGELIST,
            pageSize: COMMONPARAM_DEFAULTPAGESIZE,
            total: 0,
            pageNumber: 1,
            beforePageText: '第',
            afterPageText: '页    共 {pages} 页',
            displayMsg: '当前显示 {from} - {to}条记录,共{total}条记录'
        });

        $('#ddlTb').textbox({
            multiline: true
        })

        $('#sqlTb').textbox({
            multiline: true
        })

        $('#filterCondition').textbox('textbox').bind('keyup', function (e) {
            if (e.keyCode == 13) {
                let value = $("#filterCondition").textbox('getValue');
                queryByCondition(value);
            }
        });

        pagingList(1, COMMONPARAM_DEFAULTPAGESIZE);
    });

    function queryByCondition(condition) {
        pagingList(1, COMMONPARAM_DEFAULTPAGESIZE, condition);
    }

    function pagingList(pageNumber, pageSize, condition, sortStr) {
        let submitData = {};
        submitData['table'] = table;
        submitData['databaseRandomStr'] = databaseRandomStr;
        if (condition != undefined && condition.trim() != '') {
            submitData['condition'] = condition;
        }
        if (sortStr != undefined) {
            submitData['sortStr'] = sortStr;
        }
        submitData['page'] = pageNumber;
        submitData['limit'] = pageSize;
        postJSON(ctxPath + '/table/pagingList', submitData,
            function (data) {
                let resultList = data['resultList'];
                primaryKey = resultList[0]['primaryKey'];
                columnType = resultList[0]['columnType'];
                console.log(primaryKey);
                $('#dg').datagrid({
                    data: resultList[0]['result']
                    , columns: [resultList[0]['columns']]
                })
                $('#pg').pagination({
                    pageList: COMMONPARAM_PAGELIST,
                    pageSize: resultList[0]['pageSize'],
                    total: resultList[0]['totalCount'],
                    pageNumber: resultList[0]['pageNo'],
                    beforePageText: '第',
                    afterPageText: '页    共 {pages} 页',
                    displayMsg: '当前显示 {from} - {to}条记录,共{total}条记录',
                    onSelectPage: function (pageNumber, pageSize) {
                        pagingList(pageNumber, pageSize, condition);//选择第几页的时候，就刷新
                    }
                });
            }, function (result, resultdesc) {
                $.messager.show({
                    title: '异常',
                    msg: result + '-' + resultdesc,
                    showType: 'show'
                });
            }, function () {
                alert(3)
            }, function () {
                $.messager.progress(COMMONPARAM_PROCESS);
            }, function () {
                $.messager.progress('close');
            });
    }

    function ddl() {
        //SHOW CREATE TABLE base_inst
        let submitData = {};
        submitData['table'] = table;
        submitData['databaseRandomStr'] = databaseRandomStr;
        postJSON(ctxPath + '/table/ddl', submitData,
            function (data) {
                $('#ddlDlg').dialog('open').dialog('setTitle', 'ddl');
                $('#ddlTb').textbox('setText', data['ddl']);
            }, function (result, resultdesc) {
                $.messager.show({
                    title: '异常',
                    msg: result + '-' + resultdesc,
                    showType: 'show'
                });
            }, function () {
                alert(3)
            }, function () {
                $.messager.progress(COMMONPARAM_PROCESS);
            }, function () {
                $.messager.progress('close');
            });
    }

    function columnMode() {
        let row = $('#dg').datagrid('getSelected');
        if (row) {
            // $('#adddlg').dialog('open').dialog('setTitle', '修改用户');
            // $('#addfm').form('load', row);
            let table = "<table cellpadding='5'>"
            for (let key in row) {
                console.log(key + ":" + row[key]);
                table += "<tr>";
                table += "<td>" + key + ":</td>";
                table += "<td>" + row[key] + "</td>";
                table += "</tr>";
            }
            table += "</table>";
            //columnModeDlg
            $('#columnModeDlg').dialog('open').dialog('setTitle', '列模式');
            $('#columnModeTable').html(table);

        } else {
            $.messager.show({
                title: '提示',
                msg: '请选择一条记录!',
                showType: 'show'
            });
        }
    }

    function genralInsertSql() {
        let row = $('#dg').datagrid('getSelected');
        if (row) {
            let sql = 'insert into ' + table + ' (';
            for (let key in row) {
                if ('RN' == key) {
                    continue;
                }
                sql += key + ', ';
            }
            sql = sql.substring(0, sql.length - 2) + ') values (';
            for (let key in row) {
                //oracle的rn就不展示了
                if ('RN' == key) {
                    continue;
                }
                if (row[key] == null) {
                    sql += row[key] + ', ';
                } else {
                    if (columnType[key] == 'int' || columnType[key] == 'bigint' || columnType[key] == 'number' || columnType[key] == 'int4' ||
                        columnType[key] == 'int8') {
                        sql += row[key] + ', ';
                    } else {
                        sql += '\'' + row[key] + '\', ';
                    }
                }
            }
            sql = sql.substring(0, sql.length - 2) + ')';
            $('#sqlDlg').dialog('open').dialog('setTitle', 'insert语句');
            $('#sqlTb').textbox('setText', sql);

        } else {
            $.messager.show({
                title: '提示',
                msg: '请选择一条记录!',
                showType: 'show'
            });
        }
    }

    function genralUpdateSql() {
        let row = $('#dg').datagrid('getSelected');
        if (row) {
            let sql = 'update ' + table + ' set ';
            let columnStr = '';
            let where = 'where ';
            for (let key in row) {
                //oracle的rn就不展示了
                if ('RN' == key) {
                    continue;
                }

                let value = '';
                if (row[key] == null) {
                    value = row[key] + ' ';
                } else {
                    if (columnType[key] == 'int' || columnType[key] == 'bigint' || columnType[key] == 'number' || columnType[key] == 'int4' ||
                        columnType[key] == 'int8') {
                        value = row[key] + ' ';
                    } else {
                        value = '\'' + row[key] + '\' ';
                    }
                }

                if (primaryKey[key] != '1') {
                    columnStr += key + ' = ' + value + ', ';
                } else {
                    where += key + ' = ' + value + 'and ';
                }
            }
            if (columnStr.length > 2) {
                sql = sql + columnStr.substring(0, columnStr.length - 2) + ' ' + where.substring(0, where.length - 4);
            } else {
                sql = sql + where.substring(0, where.length - 4);
            }

            $('#sqlDlg').dialog('open').dialog('setTitle', 'update语句');
            $('#sqlTb').textbox('setText', sql);

        } else {
            $.messager.show({
                title: '提示',
                msg: '请选择一条记录!',
                showType: 'show'
            });
        }
    }

    function genralDeleteSql() {
        let row = $('#dg').datagrid('getSelected');
        if (row) {
            let sql = 'delete from ' + table + '';
            let where = 'where ';
            for (let key in row) {
                //oracle的rn就不展示了
                if ('RN' == key) {
                    continue;
                }

                let value = '';
                if (row[key] == null) {
                    value = row[key] + ' ';
                } else {
                    if (columnType[key] == 'int' || columnType[key] == 'bigint' || columnType[key] == 'number' || columnType[key] == 'int4' ||
                        columnType[key] == 'int8') {
                        value = row[key] + ' ';
                    } else {
                        value = '\'' + row[key] + '\' ';
                    }
                }

                if (primaryKey[key] != '1') {
                    //不为主键不管
                } else {
                    where += key + ' = ' + value + 'and ';
                }
            }
            sql = sql + ' ' + where.substring(0, where.length - 4);
            $('#sqlDlg').dialog('open').dialog('setTitle', 'delete语句');
            $('#sqlTb').textbox('setText', sql);

        } else {
            $.messager.show({
                title: '提示',
                msg: '请选择一条记录!',
                showType: 'show'
            });
        }
    }

    function exportData() {
        let submitData = {};
        submitData['table'] = table;
        submitData['databaseRandomStr'] = databaseRandomStr;
        let condition = $("#filterCondition").textbox('getValue');
        if (condition != undefined) {
            submitData['condition'] = condition;
        }
        postJSON(ctxPath + '/table/exportData', submitData,
            function (data) {
                //这里拿到文件的路径，然后进行导出
                let excelName = data['excelName'];
                let str = ctxPath + '/table/downloadExportData?excelName=' + excelName;
                window.open(str);
            }, function (result, resultdesc) {
                $.messager.show({
                    title: '异常',
                    msg: result + '-' + resultdesc,
                    showType: 'show'
                });
            }, function () {
                alert(3)
            }, function () {
                $.messager.progress(COMMONPARAM_PROCESS);
            }, function () {
                $.messager.progress('close');
            });
    }
</script>
</body>